package com.hiwan.util;

import java.io.File;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.Workbook;
import jxl.format.Colour;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;


/**
 * Excel工具类，主要用来把页面DataTable显示的内容输出到excel
 * 
 * @author LouShang Team
 */
public class ExcelUtil {
	private String decimalFormat = "0.00";// 保存到小数点后2位
	private String dateFormat = "yyyy-MM-dd";
	private Colour bgColor = Colour.SKY_BLUE;
	private static Logger logger = Logger.getLogger(ExcelUtil.class);
	public static String COLUMN_NAMES = "columnNames";
	public static String COLUMN_DISPLAY_NAMES = "columnLabels";
	private static int INVALID_PARAMETERS = 1;
	private static int WRITE_EXCEL_EXCEPTION = 2;
	// 默认导出路径
	private String filePath =Constants.CONFIG_SERVER_PATH + File.separator + PkUtil.getUuid() + ".xls";

	public String getFilePath() {
		return filePath;
	}

	public void setFilePath(String filePath) {
		this.filePath = filePath;
	}

	/**
	 * @return the decimalFormat
	 */
	public String getDecimalFormat() {
		return decimalFormat;
	}

	/**
	 * @param decimalFormat
	 *            the decimalFormat to set
	 */
	public void setDecimalFormat(String decimalFormat) {
		this.decimalFormat = decimalFormat;
	}

	/**
	 * @return the bgColor
	 */
	public Colour getBgColor() {
		return bgColor;
	}

	/**
	 * @param bgColor
	 *            the bgColor to set
	 */
	public void setBgColor(Colour bgColor) {
		this.bgColor = bgColor;
	}

	/**
	 * @return the dateFormat
	 */
	public String getDateFormat() {
		return dateFormat;
	}

	/**
	 * @param dateFormat
	 *            the dateFormat to set
	 */
	public void setDateFormat(String dateFormat) {
		this.dateFormat = dateFormat;
	}

	public ExcelUtil() {
	}

	public int writeListToExcel(List list, String sheetName, String columnNames, String columnDisplayNames) {
		return this.writeListToExcel(list, sheetName, columnNames, columnDisplayNames, true, null, null);
	}

	public int writeListToExcel(List list, String sheetName, String columnNames, String columnDisplayNames, boolean isDeleteFile) {
		return this.writeListToExcel(list, sheetName, columnNames, columnDisplayNames, isDeleteFile, null, null);
	}

	/**
	 * 将List中的所有对象输出到Excel, list包含的对象必须为HashMap， map中包含了所有字段和值。
	 * 
	 * 通过字段名，就可以从map中得到该字段的值，从ibatis返回的记录集就是一这种形式保存
	 * 
	 * @param list
	 * @param fileName
	 *            要输出的excel文件名，不包含路径
	 * @param sheetName
	 *            要输出到excel中的工作表名，如果为空，则取默认值sheet1
	 * @param columnNames
	 *            要输出的所有字段名，以逗号分隔
	 * @param columnDisplayNames
	 *            要输出的字段名的显示名称，通常为中文，以逗号分隔
	 * @param isDeleteFile
	 *            如果为true，则先删除目标文件，然后再写内容到该文件 如果为false， 则不删除，有时候需要在同一个excel文件输出多个sheet
	 * @param sumOnColumn
	 *            要汇总的列名
	 * @param promptStringForSum
	 *            汇总信息提示串
	 * @return 0 - 正常返回， 1 - 输入参数非法，2 - 写excel异常发生
	 */
	public int writeListToExcel(List list, String sheetName, String columnNames, String columnDisplayNames, boolean isDeleteFile, String sumOnColumn, String promptStringForSum) {
		String[] columnName = columnNames.split(",");
		String[] columnDisplayName = columnDisplayNames.split(",");
		WritableWorkbook book = null;

		if (StringUtils.isEmpty(sheetName))
			sheetName = "sheet1";

		int numberOfFields = columnName.length, result = 0;
		int[] columnWidths = new int[numberOfFields];

		try {
			String path = filePath.substring(0, filePath.lastIndexOf(File.separator));
			if (path != null && !"".equals(path)) {
				File tmp = new File(path);
				if (!tmp.isDirectory()) {
					tmp.mkdir();
				}
			}

			File file = new File(filePath);

			if (isDeleteFile && file.exists())
				file.delete();

			// 创建一个可写的工作簿
			if (file.exists()) {
				Workbook rwBook = Workbook.getWorkbook(file);
				book = Workbook.createWorkbook(file, rwBook);
			} else {
				// file.mkdir();
				book = Workbook.createWorkbook(file);
			}

			int workingSheetNo = file.exists() ? book.getNumberOfSheets() + 1 : 1;

			// 添加一个工作表
			WritableSheet ws = book.createSheet(sheetName, workingSheetNo);
			// 添加带有字型Formatting的对象,用来输出标题行
			jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false);
			jxl.write.WritableCellFormat wcf = new jxl.write.WritableCellFormat(wf);
			wcf.setBackground(bgColor);

			// 输出标题行
			for (int j = 0; j < numberOfFields; j++) {
				jxl.write.Label label = new jxl.write.Label(j, 0, columnDisplayName[j], wcf);
				ws.addCell(label);
				columnWidths[j] = columnDisplayName[j].length();
			}
			// 创建数字单元格式对象
			jxl.write.NumberFormat nf = new jxl.write.NumberFormat(decimalFormat);
			jxl.write.WritableCellFormat wcfNumber = new jxl.write.WritableCellFormat(nf);

			// 创建TimeStamp单元格式对象
			jxl.write.DateFormat dfTS = new jxl.write.DateFormat(dateFormat + " hh:mm:ss");
			jxl.write.WritableCellFormat wcfTimestamp = new jxl.write.WritableCellFormat(dfTS);
			// 创建日期单元格式对象
			jxl.write.DateFormat dfDate = new jxl.write.DateFormat(dateFormat);
			jxl.write.WritableCellFormat wcfDate = new jxl.write.WritableCellFormat(dfDate);

			int row = 1, indexForSumColumn = 0;
			double total = 0;
			boolean isNeededSum = !StringUtils.isEmpty(sumOnColumn);

			// 将List中的对象输出到excel
			for (int i = 0; i < list.size(); i++) {
				// Map map = (HashMap)
				// list.get(i);//List中的每个对象都是hashMap,map包含字段名和值
				Object _object = list.get(i);
				if (_object == null)
					continue;

				Map map = null;
				Object vo = null;

				if (_object instanceof Map) {// 判断该List对象是Map还是VO
					map = (HashMap) list.get(i);
				} else {
					vo = list.get(i);
				}
				for (int column = 0; column < numberOfFields; column++) {
					// 当对象为空时，如果继续调用jxl的API，经常会出异常，所以先检测
					Object obj = null;
					if (map != null) {
						obj = map.get(columnName[column]);
					} else if (vo != null) {
						obj = BeanUtils.getProperty(vo, columnName[column]);
					}

					if (obj == null)
						continue;

					// 如果需要统计，将从map中读到的值累加到total,同时设置统计列的位置，用于最后输出统计信息
					if (isNeededSum) {
						if (columnName[column].equals(sumOnColumn)) {
							total += Double.parseDouble(obj.toString());
							indexForSumColumn = column;
						}
					}

					// 设置列的宽度值
					if (obj.toString().length() > columnWidths[column])
						columnWidths[column] = obj.toString().length();

					if (obj instanceof String) {
						jxl.write.Label label = new jxl.write.Label(column, row, (String) obj);
						ws.addCell(label);
					} else if ((obj instanceof Integer)) {
						jxl.write.Number number = new jxl.write.Number(column, row, ((Integer) obj).doubleValue(), wcfNumber);
						ws.addCell(number);
					} else if (obj instanceof Timestamp) {
						jxl.write.DateTime datetime = new jxl.write.DateTime(column, row, (Timestamp) obj, wcfTimestamp);
						ws.addCell(datetime);
					} else if (obj instanceof BigDecimal) {
						jxl.write.Number number = new jxl.write.Number(column, row, ((BigDecimal) obj).doubleValue(), wcfNumber);
						ws.addCell(number);
					} else if (obj instanceof Double) {
						jxl.write.Number number = new jxl.write.Number(column, row, ((Double) obj).doubleValue(), wcfNumber);
						ws.addCell(number);
					} else if (obj instanceof Float) {
						jxl.write.Number number = new jxl.write.Number(column, row, ((Float) obj).doubleValue(), wcfNumber);
						ws.addCell(number);
					} else if ((obj instanceof Long)) {
						jxl.write.Number number = new jxl.write.Number(column, row, ((Long) obj).doubleValue(), wcfNumber);
						ws.addCell(number);
					} else if ((obj instanceof Short)) {
						jxl.write.Number number = new jxl.write.Number(column, row, ((Short) obj).doubleValue(), wcfNumber);
						ws.addCell(number);
					} else if (obj instanceof Date) {
						jxl.write.DateTime datetime = new jxl.write.DateTime(column, row, (Date) obj, wcfDate);
						ws.addCell(datetime);
					} else if (obj instanceof Boolean) {
						jxl.write.Boolean labelB = new jxl.write.Boolean(column, row, ((Boolean) obj).booleanValue());
						ws.addCell(labelB);
					} else {
						// 把其他类型都作为字符串处理
						jxl.write.Label label = new jxl.write.Label(column, row, (String) obj);
						ws.addCell(label);
					}
				}

				row++;
			}

			// 输出统计信息
			if (isNeededSum) {
				jxl.write.Label labelPrompt = new jxl.write.Label(indexForSumColumn - 1, row + 1, promptStringForSum);
				ws.addCell(labelPrompt);
				jxl.write.Number number = new jxl.write.Number(indexForSumColumn, row + 1, total, wcfNumber);
				ws.addCell(number);

				// 更新汇总信息的列宽度
				if (promptStringForSum.length() > columnWidths[indexForSumColumn - 1])
					columnWidths[indexForSumColumn - 1] = promptStringForSum.length();

				if (String.valueOf(total).length() > columnWidths[indexForSumColumn])
					columnWidths[indexForSumColumn] = String.valueOf(total).length();

			}

			// 调整列的宽度
			for (int i = 0; i < columnWidths.length; i++) {
				// 设定列的最大最小宽度为100和6
				if (columnWidths[i] > 100)
					columnWidths[i] = 100;
				if (columnWidths[i] < 6)
					columnWidths[i] = 6;

				ws.setColumnView(i, columnWidths[i] + 2);
			}
		} catch (Exception e) {
			result = WRITE_EXCEL_EXCEPTION;
			logger.error("写Excel失败。" + e.getMessage());
		} finally {
			try {
				if (book != null) {
					book.write();
					book.close();
				}
			} catch (Exception e) {
			}
		}

		return result;
	}
}